package com.zzxz.practicaltrainingsys.mapper;

import com.zzxz.practicaltrainingsys.vo.ViewNotice;
import com.zzxz.practicaltrainingsys.entity.Notice;
import org.apache.ibatis.annotations.*;

import java.util.List;

@Mapper
public interface NoticeMapper {
    //查询所有项目通知公告
    @Select("SELECT * FROM notice")
    List<Notice> queryAllNotice();

    //查询某个项目通知公告
    @Select("select * from notice where project_id= #{id}")
    List<Notice> getNoticeById(Integer id);

    //查询通知公告详细（多表联查项目名称）
    @Select("SELECT n.*, p.project_name, t.teacher_name " +
            "FROM notice n " +
            "LEFT JOIN project p ON n.project_id = p.project_id " +
            "LEFT JOIN teacher t ON n.teacher_id = t.teacher_id " +
            "WHERE n.notice_id = #{id}")
    ViewNotice getNoticeDetailById(@Param("id") Integer id);

    // 删除操作（根据notice_id）
    @Delete("DELETE FROM notice WHERE notice_id = #{id}")
    int deleteNoticeById(Integer id);

    // 插入操作（全字段插入）
    @Insert("INSERT INTO notice (notice_name, notice_content, notice_time, teacher_id, project_id)\n " +
            "VALUES (#{notice_name}, #{notice_content}, NOW(), #{teacher_id}, #{project_id})")
    int insertNotice(Notice notice);

    // 分页查询通知信息
    @Select("SELECT \n" +
            "  p.project_name,\n" +
            "  t.teacher_id,\n" +
            "  t.teacher_name,\n" +
            "  n.notice_content,\n" +
            "  n.notice_time,\n" +
            "  n.notice_name\n" +
            "FROM project p\n" +
            "LEFT JOIN teacher t ON p.teacher_id = t.teacher_id\n" +
            "LEFT JOIN notice n ON p.project_id = n.project_id\n" +
            "LIMIT #{pageSize} OFFSET #{offset}")
    List<ViewNotice> getAllNoticesByPage(@Param("offset") int offset, @Param("pageSize") int pageSize);

    // 查询通知总数
    @Select("SELECT COUNT(*) FROM notice")
    int getTotalNoticeCount();

    // 方案2A: 根据学生的学院、专业、年级查询相关项目通知
    @Select("SELECT " +
            "  p.project_name," +
            "  t.teacher_id," +
            "  t.teacher_name," +
            "  n.notice_content," +
            "  n.notice_time," +
            "  n.notice_name," +
            "  n.notice_id " +
            "FROM student s " +
            "JOIN project p ON (s.college_id = p.college_id AND s.major_id = p.major_id AND s.grade_id = p.grade_id) " +
            "LEFT JOIN teacher t ON p.teacher_id = t.teacher_id " +
            "LEFT JOIN notice n ON p.project_id = n.project_id " +
            "WHERE s.student_id = #{studentId} AND n.notice_id IS NOT NULL " +
            "ORDER BY n.notice_time DESC " +
            "LIMIT #{pageSize} OFFSET #{offset}")
    List<ViewNotice> getNoticesByStudentId(@Param("studentId") Integer studentId,
                                           @Param("offset") int offset,
                                           @Param("pageSize") int pageSize);

    @Select("SELECT COUNT(n.notice_id) " +
            "FROM student s " +
            "JOIN project p ON (s.college_id = p.college_id AND s.major_id = p.major_id AND s.grade_id = p.grade_id) " +
            "LEFT JOIN notice n ON p.project_id = n.project_id " +
            "WHERE s.student_id = #{studentId} AND n.notice_id IS NOT NULL")
    int getTotalNoticeCountByStudentId(@Param("studentId") Integer studentId);

    @Select({
            "<script>",
            "SELECT n.notice_id, n.notice_name, n.notice_content, n.notice_time, n.teacher_id, n.project_id, p.project_name, t.teacher_name ",
            "FROM notice n ",
            "LEFT JOIN project p ON n.project_id = p.project_id ",
            "LEFT JOIN teacher t ON n.teacher_id = t.teacher_id ",
            "<where>",
            "  <if test='project_id != null'>",
            "    n.project_id = #{project_id}",
            "  </if>",
            "</where>",
            "ORDER BY n.notice_time DESC ",
            "LIMIT #{limit} OFFSET #{offset}",
            "</script>"
    })
    List<ViewNotice> selectNoticePage(@Param("offset") int offset, @Param("limit") int limit, @Param("project_id") Integer project_id);

    @Select({
            "<script>",
            "SELECT COUNT(*) FROM notice ",
            "<where>",
            "  <if test='project_id != null'>",
            "    project_id = #{project_id}",
            "  </if>",
            "</where>",
            "</script>"
    })
    int selectNoticePageTotal(@Param("project_id") Integer project_id);;

    @Select("SELECT " +
            "n.notice_id, " +
            "n.notice_name, " +
            "n.notice_content, " +
            "n.notice_time, " +
            "n.teacher_id, " +
            "n.project_id, " +
            "p.project_name, " +
            "t.teacher_name " +
            "FROM notice n " +
            "LEFT JOIN project p ON n.project_id = p.project_id " +
            "LEFT JOIN teacher t ON n.teacher_id = t.teacher_id " +
            "WHERE n.project_id = #{projectId} " +
            "ORDER BY n.notice_time DESC")
    List<ViewNotice> getNoticesByProjectId(@Param("projectId") Integer projectId);
}